> SELECT * ...
> FROM ...
> WHERE NOT IN (SELECT contactnum FROM groups WHERE groupnum='c' or
> groupnum='d' OR ... )
>
> is bound to be _much_ faster!
Yeah, that's an obvious optimization. Unfortunately, due to needing to match semantics of a previous non-sql version
andsome pathological group specifications from clients, it can't be applied as often as I'd like. Yes, I call client
specspathological sometimes. No, they don't know that.
> And even better is
>
> SELECT *
> FROM ... contacts c1
> WHERE NOT EXISTS (SELECT * FROM groups WHERE groupname='c' or
> groupnum='d' or groupnume='e' ... AND groups.contactnum=c1.contactnum)
>
>
> EXISTS is almost always faster in PG.
Exists seems to be the answer. It even gives the right answer, which has been a problem for queries of this sort.
Rewriting the queries so that the subtraction clauses use exists are giving me reasonable runtimes (~5 sec) and
believableanswers. (instead of the other two extremes of 5 minutes and either no answers or everyone in the database)
What'suseful here is that I'm getting multiple exists index scans instead of nested loops or table scans. What's more,
existsclauses are really easy to integrate into my query generation routine.
thanks
eric